A basic spreadsheet is the electronic equivalent of a ledger book. It is divided into vertical columns and horizontal rows that intersect to form rectangles called cells. Each cell has an unique address, and is identified using the grid location. The column heading (letters A-IV) followed by the row number (1-2000) is the cell reference number. To create a spreadsheet you enter numbers (data), labels (text) and formulas into cells. Formulas are equations that describe a mathmatical relationship between numbers. They can be as simple as 2+2 or complex logical comparisons. Input different numbers into a formula and Mariner will recalculate and display new results. Mariner allows you to add graphics, text fields (post-it notes) and charts on the spreadsheet to fully explain your data.
Selecting Cells
You select a cell to tell Mariner where you want information to appear in the spreadsheet.
•To select a single cell, click on it.
•A block of cells is a range. To select a range of cells, click in one corner of the block and drag to the opposite corner. Mariner will scroll the window if you drag past the last row or column on screen.
To quickly select a large range use the Shift key.
1. Select the first cell of the range.
2. Scroll to the end of the range.
3. Hold the Shift key down and click on the last cell of the range. All of the cells will be selected.
•To select an entire row or column of cells, click in the label headings. Drag to select more than one row or column.
Entering Information
To enter information, select a cell. The active cell wil be highlighted and outlined with a solid white line. The Command Bar automatically changes when you start to type. The active cells reference number is listed below the "OK" box. Type the entry, the characters you type will appear in the cell editor below the command bar. You can click on the Command Bar icons instead of typing the character, symbol or function name.
Entering Information
•There are several ways to input the information into a cell. You can click in the "OK" box or use the keyboard, pressing the "Enter" key enters the data. The "Return" key enters the data and moves the active cell down. The "Tab" key enters data and moves the active cell right. Press the return or tab and the "Shift" key to move up or left. When a range of cells is selected the active cell is outlined in white, movement of the active cell is limited by the selected cells boundries.
•To edit the content of an existing cell click on the editor to activate it.
•Mariner treats all entries the same, unlike many spreadsheets special characters are not needed to enter numbers or formulas. Mariner looks at all cell entries in this way:
1) Is the entry a valid date? The software routine to determine a valid date is part of the system software. If you enter 1/1, January 1 of the current year will be entered and Mariner will change the cell format to a date type. Systems before 6.03 are not recommended. Dates are stored as numbers, and are based on the number of seconds before or after 12:00 AM January 1, 1904. Dates after 1904 are positive, dates before negative.
2) Is the entry a valid number? Exponential numbers are supported, for example 1.2E3 = 1200. To enter fractions use the '÷' character.3) Is the entry a valid formula? Use an "=" before the formula and Mariner will check the formula's syntax, if the formula is not understood an error dialog will be displayed.
4) If all of the conditions are false the characters in the command bar editor are entered as text.
Formulas
A formula is an algebraic symbol or combination of symbols separated by mathmatical operators. Symbols may be values, cell references or functions. Operators describe the mathmatical process applied to the quantities. Mariner limits formulas and data entries to 32,000 characters including spaces. These operator are supported, listed in order of precedence.
^ exponentiation
- negation
*, / or ÷ mult and division
+ or - addition and subtraction
|| Text string concatenation
<, >, <>,≠, <=, >=, ≤, ≥ Logical comparison
These symbols are also used and their operation performed
immediately.
: range union
! file union
'% percent operator divides current value by 100.
" used to enclose text
Mariner evaluates operators with the lowest precedence first. For example, multiplication is performed before addition. Operators with equal precedence are calculated from left to right.
However,operators enclosed with parentheses will always be calculated first. For example:
2+2*2=6
-2^2=-4
2*2^2=8
Text string concatenation
You can also use formulas to build and minipulate text strings. Text can be inserted directly into a formula by enclosing it with quotation marks, or you can put the text into a cell and use the cell reference to access it. Use two "|" characters together to concatenate two or more pieces of text into one string. For example, if cell A1 contained the text Two strings , the formula = "Join"||" "||A1 would return Join Two strings.
Cell References
When creating a formula, Mariner allows you to refer to another cell's value using relative or absolute references. The difference is only important when you copy or move the cell to another location.
•Absolute references always refer to the same cell, the address will never change.
•Mariner uses dollar signs to indicate an absolute reference, for example $A$52.
•Relative references store a cell address based on the formula's cells position with instructions how to find it. The instructions works something like this:
1. What is my current position?
2. Now go up 2 cells, turn right and go over 1 cell.
3. Get the value of this cell.
•When the formula is copied to another cell the reference will refer to a different cell address. You may mix references in any combination so any part of the cell or range has an absolute or relative address. In this reference ($A54) only the column address is absolute. If you copy this address and paste it in another cell, the column address will always be "A". The row address will use the same offset as the original and will be different in each row. A range reference has more combinations but the same rules apply (ex $A1:B$2 or A2:B$4).
•To enter a reference into a formula you may either type the cell reference or use the cell pointing feature and click on the cell. Mariner will insert the address, if a range address is needed, drag across the cells. Hold the OptionKey down if an absolute address is needed.
Named Ranges
A new feature for Mariner is the support of address names. This allows you to assign discriptive names to cells and cell ranges, using a name in a formula makes it much easier to understand. For example, you are creating a worksheet to calculate your bank balance. If you name the cells that contain the deposit, withdrawal and previous balance information, you can enter a formula such as:
=Previous_Balance+deposit-withdrawal.
This is much easier to understand than :
= A5+B5-C5, especially to someone who didn't create the spreadsheet.
To name a range, select the cells and choose Define Range... from the Option menu. Enter the name you want in the Range dialog or you can use the text in the cell to the left or above the selected cell by clicking on a checkbox. Click on "OK" and the name and cell information will be entered, when you choose that cell or range again, the address displayed in the command bar will be the your specified name.
Circular References
In a spreadsheet document a circular reference is a formula that accesses a cell that has an address that refers back to the original cell, (ie. a combin- ation of cell references that has no end). Most of the time its not that simple to find, usually there is a long chain of cell references and at the end it accesses the original cell value.
•Mariner has routines to detect circular references. If one is found, it will give a warning and trace the location of the circular references.
•The automatic calculation feature will be turned off until you resolve the circular reference.
Functions
Functions are predefined formulas, they produce a new value from other values called arguments. Arguments may be text, values, cell addresses or a combination of these. Function arguments MUST be enclosed in parentheses, each argument is separated by a comma. When the cell editor is activated, the command bar will change and the Functions menu inserted.
• Text and Empty cells are usually treated as 0 when used as a function arguments. See the function listing for specific information because some functions ignore empty cells.
•Range addresses will access the topLeft cell of the range for the argument value, unless a range reference is required by the function.
Chapter 2 - Using Mariner
Command Bar
•Mariner's Command bar allows you to point and click to choose some of the most common menu commands (see Mariner Windows- Ch 1, figure 1).
•Pattern pallettes display the current selected patterns for fills (paint can) and lines (pen). Click on either to display a pattern menu. You may tear the menu off and drag it anywhere on screen.
•The command bar may be positioned anywhere on screen.
•The close box will quit the application.
Spreadsheet Windows
• Spreadsheet windows may be divided into 4 separate panes to view different parts of a work sheet. Move the cursor over a pane control, click, hold the mouse button down and drag to the desired size.
• Click in the "Scroll Home" box to select cell A1 and scroll to the top of the worksheet.
• Hold the optionKey down and click on scrollbar arrows to scroll by printed page.
• Page breaks are displayed using a solid vertical line and a patterned horizontal line.
• Row heights and column widths are variable, click and drag between cells in the label border or select a row or column and use the size commands in the Options Menu.
Pages
The page format determines how your information is printed and displayed on screen. The page size is displayed on the worksheet, the example below shows how the same page is displayed with vertical and horizontal orientation.
•The default page is letter size with the worksheet row and column labels printed and a 1/2" margin. To change this choose the Page Setup... command in the File menu. The Show Grid and Show Label commands in the Option menu affect only the screen display, page size is not changed. Changing print options, column and row sizes will recalculate the page sizes.
• Pages are numbered vertically to the last cell entry or object, then the next page row is numbered (see the example above). If you want to know what page you are working on, enter the function PAGENUMBER into a cell.
• The content of a cell can flow into the next cell if it is empty, it will not flow into the next page.
• To specify a page break, select an entire row or column and use the Set Page Break command in the Option Menu.
Preferences
The Preference dialog allows modification of Mariners startup and operation procedures. Choose the Preferences... command in the Apple menu to see the dialog.
Features:
• Window at startup - choose between a new empty window, the Open dialog or no window when the application is started. When a file is opened this procedure is disabled.
• WorkSheet Font and Text Size - make this font and text size the default cell and text Object format in new windows.
• WorkSheet Options - with new windows show or hide labels and grids.
• Undo's - how many undo commands do you want save for each document open.
• Text Transfer - when cell information is copied to the clipboard
1) copy text and values.
2) copy text and formatted values. For example, if the cell value is 10 and is displayed with currency formatting, copy $10.00 to the clipboard.
3) copy text, values and cell formulas.
The preference file must be in the same folder that the application is in, if Mariner can not find the preference file, it will ask for permission to create a new file.
Undo
Each time you change a document Mariner saves the information needed to return the document to its previous form.
•If you make a mistake select Undo in the Edit menu to start over. Mariner allows to save up to nine undo's.
• Each WorkSheet has separate undo data.
• To reset the number of undo records saved, open the Preferences dialog (see previous section).
• Row and column size, screen display options and object order (Bring Closer) do not create an Undo record.
Formatting
Each cell in Mariner has has its own format. For all cells the format determines :
Text Font
Text Size
Text Style
Text Alignment
Cell Visibility
Cell Protection
Cells values can be displayed using these formats:
General (no format)
Scientific Notation
Percent
Fixed Decimal
Boolean (True or False)
Date (8 different styles)
Currency ($, £, L, DM, F,¥)
Hidden
• The active cells format will be checked √ in the
Format Menu.
• To change a format:
1) select the cell(s) you want to change.
2) choose the new format in the Format Menu or
Command Bar.
3) Mariner will insert the format instructions
and update the screen.
• You can specify the number of decimal places displayed. Choose Decimal Places... in the Format menu. Mariner will display up to 16 decimal places.
•Empty cells do not have format information, when you enter data into a cell Mariner will use the column format. When you change the column format, all new cell entries in the column will then use the new format. To change the column format, select the entire column by clicking in the label area, then choose a new format.
Chapter 6 - Layers and Links
Layers
Traditional spreadsheets divide data into neat rows and columns, two dimensions. Most data is not two-dimensional, which leads to less than perfect methods of organization. For example, you want to track sales data by month, product and salesperson. It is easy to slice it by product (columns) and salesperson (rows), but now you need to separate it by month and a current YTD summary would be nice too. With a 2-D spreadsheet you could solve this problem with file links and/or a large complex worksheet, but the structure of the data will still be two-dimension. Now slice the sales data by district and region and give me a company wide summary.
A Mariner worksheet is a series of 2-D spreadsheets called Layers, organized into into a single file. Each worksheet file may have a maximum of 256 layers. To access information in a different layer you simply specify a layer number, followed by the cell reference number (ex. 1:A1). A range of layers can also be specified, the formula
= SUM(1:256:A1) will total the value of cell A1 in 256 spreadsheets. The layer number of a spreadsheet is displayed in the scroll home box (see figure 1 in Getting Started). Layer references have the same features as cell references, they can use an absolute or a relative reference, or a combination of both. By using separate layers for each week, the example above can be structured to provide easy, logical access to the information needed. Each layer (spreadsheet) will use the same formulas, only the data will change.
To create, open or modify a layer, use the "Layers..." menu command in the File menu. When you open and work on a worksheet, the window for layer 1 uses the file title and will always be open. Click the close box and all windows for the worksheet will close. You may close or open any other layer individually, the window title will use the file title followed by the layer name (see example above).
Any cell or range that you have named are Global, they are valid in all worksheet layers. This makes it easy to make a template and duplicate it.
Layer Command Options
• New Layer - creates a new layer using format from the preference file.
• Import Layer - reads a text or Mariner file and places it in a new layer. If the file contains multiple layers, all layers will be added.
• Open All - will open all layers.
• Open Layer - will open only the selected layer.
• Rename layer - changes layer's window title.
• Delete Layer - delete the layer selected in the dialog list. This command is not saved in undo.
• Clone layer - create a new layer with the same row, column and print format as the selected layer.
• Duplcate layer - create a copy of the selected layer.
Save Layer As... - allows you to export the selected layer. You may save it in text or Mariner formats.
File Linking
You can also link a spreadsheet to another worksheet file. When you are creating the formula just click on the cell you want to access and Mariner will automatically add the link. File links must be on the stored on the same volume (disc). This allows you to make a copy of the original on a new disc and have it access the correct file.
•If you change the name of a file with the Save As... menu command, Mariner will ask if you also want to rename the links.
•The application will access the linked file only if it is open, otherwise the last accessed value is used. It is best if you open all link files so any formulas using the data can update.
•To open linked files, use the "Open Links..." menu command in the file menu. You can open files individually or all at once.
